//导入模块
const express=require('express');
const connection=require('../uilit/mysql')

//创建路由
const myRouter=express.Router();

//接口编写


//投递简历人数
myRouter.post('/renshu',(req,res)=>{
    const id = req.body.id;
    const sql=`SELECT total FROM (SELECT job.j_id,COUNT(*) AS total FROM job JOIN record ON job.j_id=record.j_id JOIN company ON job.c_id=company.c_id GROUP BY record.j_id) AS t1 where j_id=${id};`
    connection.query(sql,(err,data)=>{
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        }else{
            console.log(data[0])
            res.send({error:0,msg:'获取成功',t1:data[0]})
        }
})
})
//投递总简历人数
myRouter.get('/zongrenshu',(req,res)=>{
  const sql=`SELECT count(*) as total FROM job JOIN company ON job.c_id=company.c_id`
  const sql2=`SELECT  COUNT(*) AS bufen FROM(SELECT DISTINCT j_id FROM record)AS t1 JOIN job ON t1.j_id = job.j_id `
  connection.query(sql,(err,data)=>{
    if(err){
        console.log(err)
        res.send({error:1,msg:err})
    }else{
        connection.query(sql2,(err2,data2)=>{
            if(err){
                console.log(err2)
                res.send({error:1,msg:err})
            }else{
                res.send({error:0,msg:'获取成功',data:data[0],data2:data2[0]})
            }
        })
}
})
})
//获取
myRouter.get('/getdata',(req,res)=>{
    const page=req.query.page;
    const count=req.query.count;
    const sql=`SELECT * FROM job JOIN company ON job.c_id=company.c_id LIMIT ${(page-1)*count},${count}`;
    const sql2=`SELECT count(*) as total FROM job JOIN company ON job.c_id=company.c_id;`;
    connection.query(sql,(err,data)=>{
        if(err){
            console.log(err)
            res.send({error:1,msg:err})
        }else{
            connection.query(sql2,(err2,data2)=>{
                if(err2){
                    console.log(err2)
                    res.send({error:1,msg:err2})
                }else{
                    console.log(data2)
                    res.send({error:0,msg:'获取成功',data:data,total:data2[0].total})
                    }
            })
        }
    
    })

})




//查询
myRouter.post('/findout',(req,res)=>{
const page=req.body.page;
const count=req.body.count;
const sql=`SELECT * FROM job JOIN company ON job.c_id=company.c_id WHERE job_name LIKE '%${req.body.name}%' and j_date LIKE '%${req.body.time}%' LIMIT ${(page-1)*count},${count}`;
const sql2=`SELECT count(*) as total FROM job JOIN company ON job.c_id=company.c_id WHERE job_name LIKE '%${req.body.name}%' and j_date LIKE '%${req.body.time}%' `;
connection.query(sql,(err,data)=>{
    if(err){
        console.log(err)
        res.send({error:1,msg:err})
    }else{
        connection.query(sql2,(err2,data2)=>{
            if(err2){
                console.log(err2)
                res.send({error:1,msg:err2})
            }else{
                console.log(data2)
                res.send({error:0,msg:'查询成功',data:data,total:data2[0].total})
            }
        })
    }
})
})
//导出
module.exports=myRouter;